﻿//----------------------------------------------------------------------------
// Copyright (c) Pavel Tishkin 2012-2013.
//
// You must not remove this notice, or any other, from this software.
//----------------------------------------------------------------------------

namespace Lib.Dxp.Orm

open Lib.Dxp.Core
open System.Reflection
open System.Data
open System.Data.Common



type IBaseProviderCons = 
    abstract Connection : unit -> DbConnection
    abstract Command    : string * DbParameter[] -> DbCommand
    abstract MapParams  : DbCommand * obj[] -> DbCommand


type DefaultMsSqlProvider() = 
    static let MainConnectString = 
        let x = System.String.Format("{0}", System.Configuration.ConfigurationManager.AppSettings.["ConnectionString"])    
        let x = if isSome x then x else 
                System.Configuration.ConfigurationManager.ConnectionStrings.["ConnectionString"].ConnectionString
        Log.Debug ("Connect to", x)
        x

    interface IBaseProviderCons with
        member x.Connection()   = 
            new System.Data.SqlClient.SqlConnection(MainConnectString) :> DbConnection

        member x.Command(query, args)      = 
            let cmd = new System.Data.SqlClient.SqlCommand( CommandText = query ) :> DbCommand
            cmd.Parameters.AddRange args
            cmd

        member x.MapParams(cmd : DbCommand, xs : obj []) = 
            let cmd = cmd :?> SqlClient.SqlCommand
            cmd.Parameters.Clear()
            xs |> Array.iteri (fun i e -> 
                                    let reval = match e with
                                                | null  -> box System.DBNull.Value
                                                | _     -> e
                                    cmd.Parameters.AddWithValue(sprintf "@a%i" i, reval) |> ignore )
            upcast cmd



module DebugDataSession = 
        let CmdAsNonParamSQL (cmd : DbCommand) =
            let mutable x = cmd.CommandText;
            for i = cmd.Parameters.Count - 1 downto 0 do
                let nval = match cmd.Parameters.[i].Value with
                           | :? string as s -> spp s
                           | x -> to_s x
                //x <- x.Replace(Sql.Sformat.paramNo( i + 1 ), "{0} /* x{1}/{2} */".format(nval, i + 1, cmd.Parameters.Count))
                x <- x.Replace(cmd.Parameters.[i].ParameterName, "{0} /* x{1}/{2} */".format(nval, i + 1, cmd.Parameters.Count))
            x



///
///
type Session(provider : IBaseProviderCons) = 
   
    let LOGENABLED = true

    let mutable _connect  : DbConnection option     = None
    let mutable _transact : DbTransaction option    = None
    //static let mutable _log                         = Lib.Dxp.Core.Log()

    let getConnection () = if Option.isNone _connect then _connect <- Some <| provider.Connection ()
                           _connect.Value

    let autoOpenClose (_conn : DbConnection) f = 
        if  _conn.State <> ConnectionState.Open then
            _conn.Open(); let result = f() in _conn.Close(); result
        else 
            f()





    new () = Session(DefaultMsSqlProvider() :> IBaseProviderCons)

    member internal x.MaybeTransaction (cmd : DbCommand) = 
        if Option.isSome _transact then cmd.Transaction <- _transact.Value


    member x.Connection  with get () = getConnection()
    member x.Provider    with get () = provider
    //member x.Log         with get () = _log

    member x.Transaction with get () = if Option.isNone _transact then _transact <- Some <| x.Connection.BeginTransaction()
                                       _transact.Value


    member x.Commit()       = match _transact with | Some tr -> tr.Commit(); _transact <- None | _ -> ()
    member x.Rollback()     = match _transact with | Some tr -> tr.Rollback(); _transact <- None | _ -> ()

    member x.Open()         = x.Connection.Open()
    member x.Close()        = match _connect with  | Some _conn -> _conn.Close() | _ -> ()
                              _connect <- None

    member x.Command(query, args)   = provider.Command(query, args)
    member x.Command(query)         = provider.Command(query, [||])

    member x.exec(cmd : DbCommand)  = 
        let _conn = x.Connection
        cmd.Connection  <- _conn
        //cmd.Transaction <- x.Transaction
        //autoOpenClose _conn cmd.ExecuteNonQuery
        autoOpenClose x.Connection (fun () -> 
            if LOGENABLED then Log.Debug("EXEC \r\n" + DebugDataSession.CmdAsNonParamSQL cmd + "\r\n")
            cmd.Transaction <- x.Transaction
            let result = cmd.ExecuteNonQuery()
            x.Commit()
            result
            )


    member x.scalar(cmd : DbCommand) : 'a option = 
        cmd.Connection <- x.Connection
        autoOpenClose x.Connection (fun () -> 
            if LOGENABLED then Log.Debug("SCALAR\r\n" + DebugDataSession.CmdAsNonParamSQL cmd + "\r\n")
            x.MaybeTransaction cmd
            use reader  = cmd.ExecuteReader(CommandBehavior.SingleRow) 
            if reader.Read() then
                let row : obj[] = Array.zeroCreate reader.FieldCount
                reader.GetValues(row) |> ignore
                row.[0] |> unbox |> Some
            else 
                None)

    member x.insert(cmd : DbCommand) =
        let _conn = x.Connection
        autoOpenClose x.Connection (fun () -> 
            if LOGENABLED then Log.Debug("INSERT\r\n" + DebugDataSession.CmdAsNonParamSQL cmd + "\r\n")
            cmd.Connection  <- _conn
            cmd.Transaction <- x.Transaction
            cmd.ExecuteNonQuery() |> ignore
            use indexcmd = x.Command("SELECT @@IDENTITY AS IdentityExpr", [||]) 
            match x.scalar(indexcmd) with
            | Some x -> x.ToString() |> System.Convert.ToInt32 
            | _ -> 0
            )


    member x.rowset (cmd : DbCommand) = 
        cmd.Connection <- x.Connection
        autoOpenClose x.Connection (fun () -> 
            if LOGENABLED then Log.Debug("ROWSET\r\n" + DebugDataSession.CmdAsNonParamSQL cmd + "\r\n")
            x.MaybeTransaction cmd
            use reader  = cmd.ExecuteReader(CommandBehavior.SingleRow) 
            if reader.Read() then
                let row : obj[] = Array.zeroCreate reader.FieldCount
                reader.GetValues row |> ignore
                row
            else [||]
            )


    member x.dataset (cmd : DbCommand) =
        cmd.Connection <- x.Connection
        autoOpenClose x.Connection (fun () -> 
            if LOGENABLED then Log.Debug("DATASET\r\n" + DebugDataSession.CmdAsNonParamSQL cmd + "\r\n")
            x.MaybeTransaction cmd
            let table   = ResizeArray< obj [] >()
            use reader  = cmd.ExecuteReader() 
            while reader.Read() do
                let row : obj[] = Array.zeroCreate reader.FieldCount
                reader.GetValues row |> ignore
                table.Add row
            table
            )



